package org.example;

import lombok.Getter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ConfigPersistenceService {
    @Getter
    private DatabaseManager dbManager;

    public ConfigPersistenceService(DatabaseManager dbManager) {
        this.dbManager = dbManager;
    }

//    public void setDatabaseManager(DatabaseManager dbManager) {
//        // 可选的setter方法
//        this.dbManager.setDatabaseManager(dbManager);
//    }

    /**
     * 初始化数据库，创建所有需要的表
     */
    public void initializeDatabase() {
        try {
            // 创建所有需要的表
            String schema = new String(Files.readAllBytes(Paths.get("src/main/resources/schema.sql")));
            executeMultipleStatements(schema);

        } catch (IOException e) {
            System.err.println("初始化数据库失败: " + e.getMessage());
        }
    }

    private void executeMultipleStatements(String sql) {
        try (Statement stmt = dbManager.getConnection().createStatement()) {
            // 按分号分割SQL语句
            String[] statements = sql.split(";");
            for (String statement : statements) {
                String trimmedStmt = statement.trim();
                if (!trimmedStmt.isEmpty()) {
                    stmt.execute(trimmedStmt);
                }
            }


            // 插入新记录
            String insertSql = "INSERT INTO recent_files (file_path) VALUES (?)";
            try (
                    PreparedStatement insertStmt = dbManager.getConnection().prepareStatement(insertSql)) {
                insertStmt.setString(1, "");
                insertStmt.executeUpdate();
            }

        } catch (SQLException e) {
            System.err.println("保存最近文件失败: " + e.getMessage());
        }
    }

    public List<String> getRecentFiles() {
        List<String> recentFiles = new ArrayList<>();
        try {
            String sql = "SELECT file_path FROM recent_files ORDER BY last_opened DESC LIMIT 10";
            try (Statement stmt = dbManager.getConnection().createStatement();
                 ResultSet rs = stmt.executeQuery(sql)) {

                while (rs.next()) {
                    recentFiles.add(rs.getString("file_path"));
                }
            }

        } catch (SQLException e) {
            System.err.println("读取最近文件失败: " + e.getMessage());
        }
        return recentFiles;
    }

    public void saveConnectionConfig(String connectionName, Map<String, String> config) {
        try {
            // 删除旧记录（如果存在）
            String deleteSql = "DELETE FROM connection_configs WHERE config_name = ?";
            try (PreparedStatement deleteStmt = dbManager.getConnection().prepareStatement(deleteSql)) {
                deleteStmt.setString(1, connectionName);
                deleteStmt.executeUpdate();
            }

            // 插入新记录
            String insertSql = "INSERT INTO connection_configs " +
                    "(config_name, db_type, host, port, database_name, username) " +
                    "VALUES (?, ?, ?, ?, ?, ?)";

            try (PreparedStatement insertStmt = dbManager.getConnection().prepareStatement(insertSql)) {
                insertStmt.setString(1, connectionName);
                insertStmt.setString(2, config.get("dbType"));
                insertStmt.setString(3, config.get("host"));
                insertStmt.setString(4, config.get("port"));
                insertStmt.setString(5, config.get("database"));
                insertStmt.setString(6, config.get("user"));
                insertStmt.executeUpdate();
            }

        } catch (SQLException e) {
            System.err.println("保存连接配置失败: " + e.getMessage());
        }
    }

    public List<Map<String, Object>> getAllConnections() {
        List<Map<String, Object>> connections = new ArrayList<>();
        try {
            String sql = "SELECT * FROM connection_configs ORDER BY updated_at DESC";
            try (Statement stmt = dbManager.getConnection().createStatement();
                 ResultSet rs = stmt.executeQuery(sql)) {

                while (rs.next()) {
                    Map<String, Object> connection = new HashMap<>();
                    connection.put("name", rs.getString("config_name"));
                    connection.put("dbType", rs.getString("db_type"));
                    connection.put("host", rs.getString("host"));
                    connection.put("port", rs.getString("port"));
                    connection.put("database", rs.getString("database_name"));
                    connection.put("user", rs.getString("username"));
                    connections.add(connection);
                }
            }

        } catch (SQLException e) {
            System.err.println("读取连接配置失败: " + e.getMessage());
        }
        return connections;
    }

    /**
     * 获取数据库中所有表名
     * @return 表名列表
     * @throws SQLException SQL异常
     */
    public List<String> getAllTables() throws SQLException {
        return dbManager.getTables();
    }

    /**
     * 获取指定表的所有列名
     * @param tableName 表名
     * @return 列名列表
     * @throws SQLException SQL异常
     */
    public List<String> getTableColumns(String tableName) throws SQLException {
        return dbManager.getColumns(tableName);
    }

    /**
     * 保存映射规则到数据库
     * @param tableName 表名
     * @param rules 映射规则列表
     */
    public void saveMappingRules(String tableName, List<MappingRule> rules) {
        // 实现保存逻辑
    }

    /**
     * 从数据库加载映射规则
     * @param tableName 表名
     * @return 映射规则列表
     */
    public List<MappingRule> loadMappingRules(String tableName) {
        // 实现加载逻辑
        return null;
    }

    /**
     * 获取指定表的字段和类型映射
     * @param tableName 表名
     * @return 字段和类型的映射
     * @throws SQLException 如果获取字段类型失败
     */

    public Map<String, String> getTableColumnsWithTypes(String tableName) throws SQLException {
        return dbManager.getColumnsWithTypes(tableName);
    }

    /**
     * 根据目标表名获取映射配置
     * @param tableName 目标表名
     * @return 映射规则列表
     * @throws SQLException 如果查询失败
     */
    public List<MappingRule> getMappingRulesByTargetTable(String tableName) throws SQLException {
        return dbManager.getMappingRulesByTargetTable(tableName);
    }
}
